<?php
// +----------------------------------------------------------------------
// | INPHP
// +----------------------------------------------------------------------
// | Copyright (c) 2021 https://inphp.cc All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( https://opensource.org/licenses/MIT )
// +----------------------------------------------------------------------
// | Author: lulanyin <me@lanyin.lu>
// +----------------------------------------------------------------------
// |                        查询类
// +----------------------------------------------------------------------
namespace Inphp\DB;

use Closure;
use Inphp\DB\Model\ModelBase;
use PDO;

/**
 * 数据库查询类
 * Class Query
 * @package Inphp\DB
 */
class Query
{
    /**
     * 当前所用的连接
     * @var Connection|null
     */
    public Connection|null $connection = null;

    /**
     * 语法处理对象
     * @var Grammar|null
     */
    public Grammar|null $grammar = null;

    /**
     * 查询的表
     * @var mixed
     */
    public mixed $table_name = null;

    /**
     *
     * @var string[]
     */
    public array $columns = ['*'];

    /**
     * 条件
     * @var array
     */
    public array $wheres = [];

    /**
     * where条件参数的值  xx=?
     * @var array
     */
    public array $where_param_values = [];

    /**
     * 关联查询
     * @var array
     */
    public array $joins = [];

    /**
     * 关联查询时临时表别名
     * @var string
     */
    public string $join_as = '';

    /**
     * 分组条件
     * @var array
     */
    public array $group_by = [];

    /**
     * 排序
     * @var array
     */
    public array $order_by = [];

    /**
     * 侧重条件
     * @var array
     */
    public array $having = [];

    /**
     * 联合查询
     * @var array
     */
    public array $unions = [];

    /**
     * 字段和值数组
     * @var array
     */
    public array $sets = [];

    /**
     * 是否已属于聚合查询
     * @var bool
     */
    public bool $is_aggregate = false;

    /**
     * 最后一个自增值，多个插入的时候，会保存一个数组
     * @var array
     */
    public array $last_insert_id = [];

    /**
     * 影响行数
     * @var int
     */
    public int $affect_rows = 0;

    /**
     * 初始化查询类
     * 正常是不带数据库连接的初始化，等到真正执行语句的时候，再连接数据库
     * Query constructor.
     * @param Connection|null $connection
     */
    public function __construct(Connection|null $connection = null)
    {
        $this->connection = $connection;
        $this->grammar = new Grammar($this);
    }

    /**
     * 新起一个查询对象
     * @return Query
     */
    public function newQuery() : Query
    {
        return new static($this->connection);
    }

    /**
     * 一般都是从这方法开始使用...
     * @param mixed $table
     * @param string $as
     * @return Query
     */
    public function from(mixed $table, string $as = '') : Query
    {
        if($table instanceof Query)
        {
            $this->table_name = [
                'query' => $table,
                'as'    => $as
            ];
        }
        elseif ($table instanceof ModelBase)
        {
            $this->table_name = $table::tableName() . (!empty($as) ? " {$as}" : "");
        }
        else
        {
            $this->table_name = $table . (!empty($as) ? " {$as}" : "");
        }
        return $this;
    }

    /**
     * 查询字段
     * @param mixed $columns
     * @param bool $replace
     * @return Query
     */
    public function select(mixed $columns, bool $replace = true) : Query
    {
        $columns = is_array($columns) ? $columns : explode(",", $columns);
        $list = [];
        foreach ($columns as $column)
        {
            $column = preg_replace("/\s+/", " ", $column);
            $column = str_replace("、", ",", $column);
            $column = str_replace("，", ",", $column);
            $column = str_replace("|", ",", $column);
            $list[] = trim($column);
        }
        $this->columns = $replace ? $list : array_merge($this->columns, $list);
        return $this;
    }

    /**
     * 增加查询字段
     * @param mixed $columns
     * @return Query
     */
    public function addSelect(mixed $columns) : Query
    {
        return $this->select($columns, false);
    }

    /**
     * select any_value(xxx) 主要用于 group by
     * @param mixed $columns
     * @param mixed|array $as
     * @return Query
     */
    public function selectAnyValue(mixed $columns, mixed $as = []) : Query
    {
        $columns = is_array($columns) ? $columns : explode(",", $columns);
        $as = is_array($as) ? $as : explode(",", $as);
        $string = [];
        foreach ($columns as $key=>$column)
        {
            $string[] = "any_value({$column})".(!empty($as[$key]) ? " as {$as[$key]}" : "");
        }
        return $this->addSelect($string);
    }

    /**
     * 关联查询
     * @param mixed $table_name
     * @param mixed $on
     * @param string $type
     * @return Query
     */
    public function join(mixed $table_name, mixed $on, string $type = 'inner') : Query
    {
        $status = 'default';
        $as = '';
        if($table_name instanceof Query)
        {
            //->join(Query->from(....))...
            list($query_string, $params) = $table_name->compileToQueryString();
            $this->grammar->flushParams($params);
            $as = $table_name->join_as;
            if(empty($as))
            {
                $this->getConnection()->log("关联嵌套查询时，缺少别名：\r\n{$query_string}");
            }
            $table = "({$query_string}) as {$as}";
            //嵌套查询
            $status = 'nested';
        }
        elseif($table_name instanceof Closure)
        {
            //->join(function(newQuery){ ... })...
            $newQuery = $this->newQuery();
            call_user_func($table_name, $newQuery);
            list($query_string, $params) = $newQuery->compileToQueryString();
            $this->grammar->flushParams($params);
            $as = $newQuery->join_as;
            if(empty($as))
            {
                $this->getConnection()->log("关联嵌套查询时，缺少别名：\r\n{$query_string}");
            }
            $table = "({$query_string}) as {$as}";
            //嵌套查询
            $status = 'nested';
        }
        else
        {
            $table = $table_name;
        }
        $this->joins[] = compact('table', 'as', 'on', 'type', 'status');
        return $this;
    }

    /**
     * 左关联
     * @param mixed $table_name
     * @param mixed $on
     * @return Query
     */
    public function leftJoin(mixed $table_name, mixed $on) : Query
    {
        return $this->join($table_name, $on, 'left');
    }

    /**
     * 右关联
     * @param mixed $table_name
     * @param mixed $on
     * @return Query
     */
    public function rightJoin(mixed $table_name, mixed $on) : Query
    {
        return $this->join($table_name, $on, 'right');
    }

    /**
     * 临时表别名
     * @param string $as
     * @return Query
     */
    public function joinAs(string $as) : Query
    {
        $this->join_as = $as;
        return $this;
    }

    /**
     * where ....
     * @param mixed $column
     * @param mixed $operator
     * @param mixed $value
     * @param string $boolean
     * @return Query
     */
    public function where(mixed $column, mixed $operator = '=', mixed $value = '', string $boolean = 'and') : Query
    {
        if(is_array($column)){
            $column = is_array(end($column)) ? $column : [$column];
            foreach ($column as $c){
                $len = count($c);
                if($len == 2){
                    $c[2] = $c[1];
                    $c[1] = "=";
                    $c[3] = "and";
                }elseif($len == 3){
                    $c[3] = "and";
                }elseif($len == 1){
                    $c[1] = $c[2] = null;
                    $c[3] = "and";
                }
                @$this->where($c[0], $c[1], $c[2], $c[3]);
            }
            return $this;
        }
        if(func_num_args() == 2){
            list($operator, $value) = ['=', $operator];
        }

        if($column instanceof Closure){
            return $this->whereNested($column, $boolean);
        }

        if($value instanceof Closure){
            return $this->whereSub($column, $operator, $value, $boolean);
        }

        if (is_null($value)) {
            return $this->whereNull($column, $boolean, $operator != '=');
        }

        $type = 'Basic';
        $this->wheres[] = compact('type', 'column', 'operator', 'value', 'boolean');
        return $this;
    }

    /**
     * or
     * @param mixed $column
     * @param mixed $operator
     * @param string $value
     * @return Query
     */
    public function orWhere(mixed $column, mixed $operator = '=', string $value = '') : Query
    {
        if(func_num_args() == 2)
        {
            list($value, $operator) = [$operator, '='];
        }
        return $this->where($column, $operator, $value, 'or');
    }

    /**
     * 原生SQL条件
     * @param string $sql
     * @param string $boolean
     * @return Query
     */
    public function whereRaw(string $sql, string $boolean = 'and') : Query
    {
        $type = 'Raw';
        $this->wheres[] = compact('type', 'sql', 'boolean');
        return $this;
    }

    /**
     * or 原生条件
     * @param string $sql
     * @return $this
     */
    public function orWhereRaw(string $sql) : Query
    {
        return $this->whereRaw($sql, 'or');
    }

    /**
     * 子查询
     * @param mixed $column
     * @param mixed $operator
     * @param Closure $closure
     * @param string $boolean
     * @return $this
     */
    public function whereSub(mixed $column, mixed $operator, Closure $closure, string $boolean = 'and') : Query
    {
        $type = 'Sub';
        $query = $this->newQuery();
        call_user_func($closure, $query);
        $this->wheres[] = compact('type', 'column', 'operator', 'query', 'boolean');
        return $this;
    }

    /**
     * 嵌套查询
     * @param Closure $callBack
     * @param string $boolean
     * @return Query
     */
    public function whereNested(Closure $callBack, string $boolean = 'and') : Query
    {
        $query = $this->newQuery();
        $query->from($this->table_name);
        call_user_func($callBack, $query);
        return $this->addNestedWhereQuery($query, $boolean);
    }

    /**
     *
     * @param Query $query
     * @param string $boolean
     * @return Query
     */
    public function addNestedWhereQuery(Query $query, string $boolean = 'and') : Query
    {
        if (count($query->wheres)) {
            $type = 'Nested';
            $this->wheres[] = compact('type', 'query', 'boolean');
        }
        return $this;
    }

    /**
     * 字段值是否为 null 的条件
     * @param string $column
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereNull(string $column, string $boolean='and', bool $not = false) : Query
    {
        $type = $not ? 'NotNull' : 'Null';
        $this->wheres[] = compact('type', 'column', 'boolean');
        return $this;
    }

    /**
     * or 字段为 null
     * @param string $column
     * @param bool $not
     * @return Query
     */
    public function orWhereNull(string $column, bool $not = false) : Query
    {
        return $this->whereNull($column, 'or', $not);
    }

    /**
     * 字段不为null
     * @param string $column
     * @param string $boolean
     * @return Query
     */
    public function whereNotNull(string $column, string $boolean = 'and') : Query
    {
        return $this->whereNull($column, $boolean, true);
    }

    /**
     * or 字段不为null
     * @param string $column
     * @return Query
     */
    public function orWhereNotNull(string $column) : Query
    {
        return $this->orWhereNull($column, true);
    }

    /**
     * 字段值是否为空的条件
     * @param string $column
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereEmpty(string $column, string $boolean='and', bool $not = false) : Query
    {
        return $this->where(function($eq) use($column, $not, $boolean){
            $eq->where($column, $not ? '!=' : '=', '', $boolean);
            if($not){
                $eq->whereNull($column, 'and', true);
            }else{
                $eq->orWhereNull($column, false);
            }
        }, null, null, $boolean);
    }

    /**
     * or 字段为空
     * @param string $column
     * @param bool $not
     * @return Query
     */
    public function orWhereEmpty(string $column, bool $not = false) : Query
    {
        return $this->whereEmpty($column, 'or', $not);
    }

    /**
     * 字段不为空
     * @param string $column
     * @param string $boolean
     * @return Query
     */
    public function whereNotEmpty(string $column, string $boolean = 'and') : Query
    {
        return $this->whereEmpty($column, $boolean, true);
    }

    /**
     * or 字段不为空
     * @param string $column
     * @return Query
     */
    public function orWhereNotEmpty(string $column) : Query
    {
        return $this->orWhereEmpty($column, true);
    }

    /**
     * @param string $column
     * @param mixed $value
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereIn(string $column, mixed $value, string $boolean = 'and', bool $not = false) : Query
    {
        if($value instanceof Closure){
            return $this->whereInSub($column, $value, $boolean, $not);
        }
        $type = $not ? 'NotIn' : 'In';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }

    /**
     * in 条件
     * @param string $column
     * @param Closure $callback
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereInSub(string $column, Closure $callback, string $boolean = 'and', bool $not = false) : Query
    {
        $type = $not ? 'NotIn' : 'In';
        $query = $this->newQuery();
        call_user_func($callback, $query);
        $this->wheres[] = compact('type', 'column', 'query', 'boolean');
        return $this;
    }

    /**
     * not in
     * @param string $column
     * @param mixed $value
     * @param string $boolean
     * @return Query
     */
    public function whereNotIn(string $column, mixed $value, string $boolean = 'and') : Query
    {
        return $this->whereIn($column, $value, $boolean, true);
    }

    /**
     * or in
     * @param string $column
     * @param mixed $value
     * @param bool $not
     * @return Query
     */
    public function orWhereIn(string $column, mixed $value, bool $not = false) : Query
    {
        return $this->whereIn($column, $value, 'or', $not);
    }

    /**
     * or not in
     * @param string $column
     * @param mixed $value
     * @return Query
     */
    public function orWhereNotIn(string $column, mixed $value) : Query
    {
        return $this->orWhereIn($column, $value, true);
    }

    /**
     * between 条件
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereBetween($column, $value, string $boolean='and', bool $not = false) : Query
    {
        $type = $not ? 'NotBetween' : 'Between';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }

    /**
     * xx not between a and b
     * @param $column
     * @param $value
     * @param string $boolean
     * @return Query
     */
    public function whereNotBetween($column, $value, string $boolean = 'and') : Query
    {
        return $this->whereBetween($column, $value, $boolean, true);
    }

    /**
     * or xx between a and b
     * @param $column
     * @param $value
     * @param bool $not
     * @return Query
     */
    public function orWhereBetween($column, $value, bool $not = false) : Query
    {
        return $this->whereBetween($column, $value, 'or', $not);
    }

    /**
     * or xx not between a and b
     * @param $column
     * @param $value
     * @return Query
     */
    public function orWhereNotBetween($column, $value) : Query
    {
        return $this->orWhereBetween($column, $value, true);
    }

    /**
     * exists条件
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereExists($value, string $boolean = 'and', bool $not = false) : Query
    {
        if($value instanceof Closure){
            return $this->whereExistsSub($value, $boolean, $not);
        }else{
            return $this;
        }
    }

    /**
     * not exists
     * @param $value
     * @param string $boolean
     * @return Query
     */
    public function whereNotExists($value, string $boolean = 'and') : Query
    {
        return $this->whereExists($value, $boolean, true);
    }

    /**
     * or exists
     * @param $value
     * @param bool $not
     * @return Query
     */
    public function orWhereExists($value, bool $not = false) : Query
    {
        return $this->whereExists($value, 'or', $not);
    }

    /**
     * or not exists
     * @param $value
     * @return Query
     */
    public function orWhereNotExists($value) : Query
    {
        return $this->orWhereExists($value, true);
    }

    /**
     * exists (sql ...)
     * @param Closure $callback
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereExistsSub(Closure $callback, string $boolean = 'and', bool $not = false) : Query
    {
        $type = $not ? 'NotExists' : 'Exists';
        $query = $this->newQuery();
        call_user_func($callback, $query);
        $this->wheres[] = compact('type', 'query', 'boolean');
        return $this;
    }

    /**
     * find_in_set
     * @param $value
     * @param $column
     * @param string $boolean
     * @return Query
     */
    public function whereFindInSet($value, $column, string $boolean = 'and') : Query
    {
        $type = 'FindInSet';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }

    /**
     * or find_in_set
     * @param $value
     * @param $column
     * @return Query
     */
    public function orWhereFindInSet($value, $column) : Query
    {
        return $this->whereFindInSet($value, $column, 'or');
    }

    /**
     * like 查询条件
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereLike($column, $value, string $boolean = 'and', bool $not = false) : Query
    {
        $type = $not ? 'NotLike' : 'Like';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }

    /**
     * or like
     * @param $column
     * @param $value
     * @param bool $not
     * @return Query
     */
    public function orWhereLike($column, $value, bool $not = false) : Query
    {
        return $this->whereLike($column, $value, "or", $not);
    }

    /**
     * not like
     * @param $column
     * @param $value
     * @return Query
     */
    public function whereNotLike($column, $value) : Query
    {
        return $this->whereLike($column, $value, 'and', true);
    }

    /**
     * or not like
     * @param $column
     * @param $value
     * @return Query
     */
    public function orWhereNotLike($column, $value) : Query
    {
        return $this->orWhereLike($column, $value, true);
    }

    /**
     * 正则匹配
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return Query
     */
    public function whereRegexp($column, $value, string $boolean = 'and', bool $not = false) : Query
    {
        $type = $not ? 'NotRegexp' : 'Regexp';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }

    /**
     * or regexp
     * @param $column
     * @param $value
     * @param bool $not
     * @return Query
     */
    public function orWhereRegexp($column, $value, bool $not = false) : Query
    {
        return $this->whereRegexp($column, $value, "or", $not);
    }

    /**
     * not regexp
     * @param $column
     * @param $value
     * @return Query
     */
    public function whereNotRegexp($column, $value) : Query
    {
        return $this->whereRegexp($column, $value, 'and', true);
    }

    /**
     * or not regexp
     * @param $column
     * @param $value
     * @return Query
     */
    public function orWhereNotRegexp($column, $value) : Query
    {
        return $this->orWhereRegexp($column, $value, true);
    }

    /**
     * 判断以 逗号 , 分隔的字符与字段值是否 有交集
     * @param $column
     * @param $value
     * @param bool $not
     * @return Query
     */
    public function whereIsIntersection($column, $value, bool $not = false) : Query
    {
        $value = is_array($value) ? join(",", $value) : $value;
        if(!empty($value) && !empty($column)){
            return $this->whereRaw("(select concat('{$value}', ',') regexp concat(replace({$column}, ',', ',|'), ',')) = ".($not ? "0" : "1"));
        }
        return $this;
    }

    /**
     * 判断以 逗号 , 分隔的字符与字段值是否 无交集
     * @param $column
     * @param $value
     * @return Query
     */
    public function whereNotIntersection($column, $value) : Query
    {
        return $this->whereIsIntersection($column, $value, true);
    }

    /**
     * 转换为 datetime 可接受的格式
     * @param $time
     * @return string
     */
    private function translateToDatetime($time) : string
    {
        if(is_numeric($time) && strlen($time)==10){
            return date("Y-m-d H:i:s", $time);
        }
        $time = preg_replace("/[年|月|\/]$/", "-", $time);
        $time = str_replace("日", " ", $time);
        $time = preg_replace("/[时|分]$/", ":", $time);
        $time = str_replace("秒", "", $time);
        return str_replace("--", "-", trim($time));
    }

    /**
     * datetime 字段专用的时间段查询，可以
     * @param $column
     * @param $start
     * @param $end
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereDateTimeBetween($column, $start, $end, bool $datetime = true) : Query
    {
        $start = $datetime ? $this->translateToDatetime($start) : $start;
        $end = $datetime ? $this->translateToDatetime($end) : $end;
        return $this->whereBetween($column, ["'{$start}'", "'{$end}'"]);
    }

    /**
     * 从什么时间开始
     * @param $column
     * @param $start
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereDateTimeStartAt($column, $start, bool $datetime = true) : Query
    {
        $start = $datetime ? $this->translateToDatetime($start) : $start;
        return $this->where($column, ">=", $start);
    }

    /**
     * 到什么时间结束
     * @param $column
     * @param $end
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereDateTimeEndAt($column, $end, bool $datetime = true) : Query
    {
        $end = $datetime ? $this->translateToDatetime($end) : $end;
        return $this->where($column, "<=", $end);
    }

    /**
     * 查询，过去N个月
     * @param $column
     * @param $number
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereMonthAfter($column, $number, bool $datetime = true) : Query
    {
        $number = intval($number);
        if($number>0){
            $column = $datetime ? $column : "from_unixtime({$column})";
            $this->whereRaw("{$column} between date_sub(now(), interval {$number} month) and now()");
        }
        return $this;
    }

    /**
     * 查询过去 N 个月之前的数据
     * @param $column
     * @param $number
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereMonthBefore($column, $number, bool $datetime = true) : Query
    {
        $number = intval($number);
        if($number>0){
            $column = $datetime ? $column : "from_unixtime({$column})";
            $this->whereRaw("{$column}<=date_sub(now(), interval {$number} month)");
        }
        return $this;
    }

    /**
     * 查询过去 N1 个月份到 N2 个月份之内的数据
     * @param $column
     * @param $month
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     * ->whereMonthBetween("时间字段", [6, 12]) 查询过去 6~12个月份内的数据
     * ->whereMonthBetween("时间字段", 6) 查询过去 6 个月内的数据
     */
    public function whereMonthBetween($column, $month, bool $datetime = true) : Query
    {
        $month = is_array($month) ? $month : [intval($month)];
        $start = $month[1] ?? $month[0];
        $start = "date_sub(now(), interval {$start} month)";
        $end = isset($month[1]) ? "date_sub(now(), interval {$month[0]} month)" : "now()";
        $column = $datetime ? $column : "from_unixtime({$column})";
        $this->whereRaw("{$column} between {$start} and {$end}");
        return $this;
    }

    /**
     * 查询过去 N 年之前的数据
     * @param $column
     * @param $number
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereYearBefore($column, $number, bool $datetime = true) : Query
    {
        $number = intval($number);
        if($number>0){
            $column = $datetime ? $column : "from_unixtime({$column})";
            $this->whereRaw("{$column}<=date_sub(now(), interval {$number} year)");
        }
        return $this;
    }

    /**
     * 查询过去 N1 个年份到 N2 个年份之内的数据
     * @param $column
     * @param $year
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereYearBetween($column, $year, bool $datetime = true) : Query
    {
        $year = is_array($year) ? $year : [intval($year)];
        $start = $year[1] ?? $year[0];
        $start = "date_sub(now(), interval {$start} year)";
        $end = isset($year[1]) ? "date_sub(now(), interval {$year[0]} year)" : "now()";
        $column = $datetime ? $column : "from_unixtime({$column})";
        $this->whereRaw("{$column} between {$start} and {$end}");
        return $this;
    }

    /**
     * 查询N周之前的，默认为本周
     * @param $column
     * @param int $number
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereWeek($column, $number = 0, bool $datetime = true) : Query
    {
        $column = $datetime ? $column : "from_unixtime({$column})";
        return $this->whereRaw("YEARWEEK({$column}, 1) = YEARWEEK(NOW(), 1)-{$number}");
    }

    /**
     * 查询N天之前的数据，默认今天
     * @param $column
     * @param int $number
     * @param bool $datetime 字段是否是datetime类型，如果不是，默认使用int 10位长度去处理
     * @return Query
     */
    public function whereDay($column, int $number = 0, bool $datetime = true) : Query
    {
        $column = $datetime ? $column : "from_unixtime({$column})";
        return $this->whereRaw("TO_DAYS(NOW())-TO_DAYS({$column})<={$number}");
    }

    /**
     * union
     * @param Query $query
     * @param bool $all
     * @return Query
     */
    public function union(Query $query, bool $all = false) : Query
    {
        $this->unions[] = compact('query', 'all');
        return $this;
    }

    /**
     * union all
     * @param Query $query
     * @return Query
     */
    public function unionAll(Query $query) : Query
    {
        return $this->union($query);
    }

    /**
     * 侧重查询
     * @param $column
     * @param $operator
     * @param null $value
     * @return Query
     */
    public function having($column, $operator, $value=null) : Query
    {
        if(func_num_args()==2){
            list($value, $operator) = [$operator, '='];
        }
        $this->having[] = compact('column', 'operator', 'value');
        return $this;
    }

    /**
     * group by
     * @param $columns
     * @return Query
     */
    public function groupBy($columns) : Query
    {
        $this->group_by[] = $columns;
        return $this;
    }

    /**
     * order by
     * @param $column
     * @param string $type
     * @return Query
     */
    public function orderBy($column, string $type = 'ASC') : Query
    {
        if(is_array($column)){
            foreach($column as $col){
                if(!empty($col[0])){
                    $this->order_by[] = array(
                        "column" => $col[0],
                        "type" => $col[1] ?? $type
                    );
                }
            }
        }else{
            $this->order_by[] = is_array($column) ? $column : compact('column', 'type');
        }
        return $this;
    }

    /**
     * 解析成SQL语句
     * @param string $type
     * @return array
     */
    public function compileToQueryString(string $type = 'select') : array
    {
        return $this->grammar->compileToQueryString($type);
    }

    /**
     * 读取数据
     * @param int $total
     * @param int $offset
     * @return array|false
     */
    public function get(int $total = 0, int $offset = 0) : array|false
    {
        return $this->grammar->get($total, $offset);
    }

    /**
     * 读取第一条数据
     * @return array|false
     */
    public function first() : array|false
    {
        $row = $this->get(1);
        if(is_array($row)){
            return is_array(end($row)) ? end($row) : [];
        }
        return false;
    }

    /**
     * 读取前X条
     * @param int $total
     * @return array|false
     */
    public function top(int $total = 1) : array|false
    {
        return $this->get($total);
    }

    /**
     * 获取行数
     * @return int
     */
    public function count() : int
    {
        return $this->grammar->count();
    }

    /**
     * 获取行数
     * @return int
     */
    public function rows() : int
    {
        return $this->count();
    }

    /**
     * @param $column
     * @param mixed $value
     * @param bool $valueIncludeField
     * @return Query
     */
    public function set($column, mixed $value = null, bool $valueIncludeField = false) : Query
    {
        if(is_array($column)){
            $columns = is_array(end($column)) ? $column : [$column];
            foreach($columns as $index=>$column){
                foreach($column as $key => $val){
                    $this->sets[$index][] = [
                        'field' => $key,
                        'value' => $val,
                        'include_field' => $value === true || $value === false ? $value : $valueIncludeField
                    ];
                }
            }
        }else{
            $this->sets[0][] = [
                'field' => $column,
                'value' => $value,
                'include_field' => $valueIncludeField
            ];
        }
        return $this;
    }

    /************************** 开始执行方法 **************************/

    /**
     * 执行更新数据库语句
     * @param array|null $columns
     * @return bool
     */
    public function update(array|null $columns = null) : bool
    {
        if(is_array($columns)){
            $this->set($columns);
        }
        return $this->grammar->update();
    }

    /**
     * 执行插入数据操作
     * @param array|null $sets
     * @return bool
     */
    public function insert(array|null $sets = null) : bool
    {
        if(is_array($sets)){
            $this->set($sets);
        }
        return $this->grammar->insert();
    }

    /**
     * 当条件数据不存在时，插入数据
     * @return bool
     */
    public function insertIfNotExists() : bool
    {
        return $this->grammar->insertIfNotExists();
    }

    /**
     * 插入数据并返回自增ID，失败时返回0
     * @param array|null $sets
     * @return int
     */
    public function insertGetId(array|null $sets = null) : int
    {
        if($this->insert($sets)){
            return $this->getLastInsertId();
        }else{
            return 0;
        }
    }

    /**
     * 获取自增ID
     * @return int|array
     */
    public function getLastInsertId() : int|array
    {
        return count($this->last_insert_id) > 1 ? $this->last_insert_id : ($this->last_insert_id[0] ?? 0);
    }

    /**
     * 删除数据
     * @return bool
     */
    public function delete() : bool
    {
        return $this->grammar->delete();
    }

    /**
     * 获取影响的记录条数
     * @return int
     */
    public function getAffectRows() : int
    {
        return $this->affect_rows;
    }

    /**
     * 获取错误
     * @return string
     */
    public function getError() : string
    {
        return $this->getConnection()->getError();
    }

    /**
     * 从数据表中取得单一行数据的单一字段
     * @param string $column
     * @return string
     */
    public function pluck(string $column) : string
    {
        $column = $this->getConnection()->parseColumns($column);
        $select = "{$column} as pluck_field";
        $this->columns = [$select];
        $firstRow = $this->first();
        return $firstRow["pluck_field"] ?? "";
    }

    /**
     * 从数据表中取得单一行数据列的单一字段值
     * @param string $column
     * @return string
     */
    public function pick(string $column) : string
    {
        return $this->pluck($column);
    }

    /**
     * 取得单一字段值的列表
     * @param string $column    要取的字段
     * @param bool $distinct    是否需要唯一值
     * @param int $total    是否需要唯一值
     * @param int $offset    是否需要唯一值
     * @return array
     */
    public function lists(string $column, bool $distinct = false, int $total = 0, int $offset = 0) : array
    {
        $column = $this->getConnection()->parseColumns($column);
        $this->columns = [($distinct ? "distinct " : "")."{$column} as list_column"];
        $rows = $this->get($total, $offset);
        $list = [];
        foreach($rows as $r){
            if(isset($r["list_column"])){
                $list[] = $r["list_column"];
            }
        }
        return $list;
    }

    /**
     * 获取聚合函数统计的值
     * @param $column
     * @param $fn
     * @return string
     */
    protected function aggregateFunction($column, $fn) : string
    {
        $column = $this->getConnection()->parseColumns($column);
        $string = $fn."({$column}) as val";
        $this->columns = [$string];
        $this->is_aggregate = true;
        $row = $this->first();
        return $row["val"] ?? "";
    }

    /**
     * 检测是否属于聚合查询
     * @return bool
     */
    public function checkAggregateQuery() : bool
    {
        if($this->is_aggregate){
            return true;
        }
        $columns = $this->columns;
        $columnsString = join(",", $columns);
        return strripos($columnsString, "sum(")
            || strripos($columnsString, "avg(")
            || strripos($columnsString, "count(")
            || strripos($columnsString, "max(")
            || strripos($columnsString, "min(");
    }

    /**
     * 获取字段最大值
     * @param $column
     * @return float
     */
    public function max($column) : float
    {
        $val = $this->aggregateFunction($column, "max");
        return !empty($val) ? floatval($val) : 0;
    }

    /**
     * 获取字段的最小值
     * @param $column
     * @return float
     */
    public function min($column) : float
    {
        $val = $this->aggregateFunction($column, "min");
        return !empty($val) ? floatval($val) : 0;
    }

    /**
     * 获取平均值
     * @param $column
     * @param int $decimal
     * @return float
     */
    public function avg($column, int $decimal = -1) : float
    {
        $val = $this->aggregateFunction($column, "avg");
        return !empty($val) ? ($decimal == 0 ? floor($val) : ($decimal > 0 ? floor($val * pow(10, $decimal))/pow(10, $decimal) : $val)) : 0;
    }

    /**
     * 获取总和
     * @param $column
     * @return float
     */
    public function sum($column) : float
    {
        $val = $this->aggregateFunction($column, "sum");
        return !empty($val) ? floatval($val) : 0;
    }

    /**
     * 快速清空数据表，并重置自增ID
     * @return bool
     */
    public function truncate() : bool
    {
        return $this->grammar->truncate();
    }

    /**
     * 原生SQL赋值
     * @param string $column
     * @param $value
     * @return Query
     */
    public function setRaw(string $column, $value) : Query
    {
        return $this->set($column, $value, true);
    }

    /**
     * 自增一个字段，可以同时更新其它字段
     * @param string $column
     * @param float $number
     * @return Query
     */
    public function increment(string $column, float $number = 1) : Query
    {
        if($number == 0)
        {
            return $this;
        }
        if($number < 0)
        {
            return $this->decrement($column, abs($number));
        }
        return $this->setRaw($column, "`{$column}`+{$number}");
    }

    /**
     * 自减一个字段，可以同时更新其它字段
     * @param string $column
     * @param float $number
     * @return Query
     */
    public function decrement(string $column, float $number = 1) : Query
    {
        if($number == 0)
        {
            return $this;
        }
        if($number < 0)
        {
            return $this->increment($column, abs($number));
        }
        return $this->setRaw($column, "`{$column}`-{$number}");
    }

    /**
     * 获取数据库连接
     * @return Connection
     */
    public function getConnection() : Connection
    {
        if(is_null($this->connection)){
            $this->connection = DB::getConnection();
        }
        return $this->connection;
    }

    /**
     * 获取PDO
     * @param string $type
     * @return PDO|false
     */
    public function getPdo(string $type = 'read') : PDO|false
    {
        return $this->getConnection()->getPdo($type);
    }
}